﻿using Dapper;
using CMS.Model;
using MySql.Data.MySqlClient;
using Microsoft.AspNetCore.Identity;
using System.Threading.Tasks;
using System.Data;
using System.Collections.Generic;
using System;

namespace CMS.Data.Repository
{
    public class UserRepository : BaseRepository
    {
        /// <summary>
        /// 创建用户
        /// </summary>
        /// <param name="user">用户</param>
        public async Task<IdentityResult> CreateAsync(User user)
        {
            int rows;
            using (var db = GetMySqlConnection())
            {
                rows = await db.ExecuteAsync(GetInsertSQL(user, "Users"), commandType: CommandType.Text);
            }
            if (rows > 0)
            {
                return IdentityResult.Success;
            }
            return IdentityResult.Failed(new IdentityError { Description = $"Could not insert user {user.UserName}." });
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="user">用户</param>
        public async Task<IdentityResult> DeleteAsync(User user)
        {
            int rows;
            using (var db = GetMySqlConnection())
            {
                rows = await db.ExecuteAsync("update Users set Deleted = 1 Where Id = @Id", new { user.Id }, commandType: CommandType.Text);
            }
            if (rows > 0)
            {
                return IdentityResult.Success;
            }
            return IdentityResult.Failed(new IdentityError { Description = $"Could not delete user {user.UserName}." });
        }

        /// <summary>
        /// 更新用户
        /// </summary>
        /// <param name="user">用户</param>
        /// <returns></returns>
        public async Task<IdentityResult> UpdateAsync(User user)
        {
            int rows;
            using (var db = GetMySqlConnection())
            {
                rows = await db.ExecuteAsync(GetUpdateSQL(user, "Users", $"Id = @Id"), new { user.Id }, commandType: CommandType.Text);
            }
            if (rows > 0)
            {
                return IdentityResult.Success;
            }
            return IdentityResult.Failed(new IdentityError { Description = $"Could not update user {user.UserName}." });
        }


        /// <summary>
        /// 根据微信OpenId查找用户
        /// </summary>
        /// <param name="userId">userId</param>
        /// <returns></returns>
        public async Task<User> FindByWXOpneId(string opneId)
        {
            using (var db = GetMySqlConnection())
            {
                return await db.QuerySingleOrDefaultAsync<User>("select * from Users where WXOpenID = @opneId", new { opneId });
            }
        }


        /// <summary>
        /// 查找用户
        /// </summary>
        /// <param name="userId">userId</param>
        /// <returns></returns>
        public async Task<User> FindByIdAsync(Guid Id)
        {
            using (var db = GetMySqlConnection())
            {
                return await db.QuerySingleOrDefaultAsync<User>("select * from Users where Id = @Id", new { Id });
            }
        }

        /// <summary>
        /// 查找用户
        /// </summary>
        /// <param name="userId">userId</param>
        /// <returns></returns>
        public async Task<User> FindByNameAsync(string userName)
        {
            using (var db = GetMySqlConnection())
            {
                return await db.QuerySingleOrDefaultAsync<User>("select * from Users where UserName = @userName", new { userName });
            }
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <returns></returns>
        public async Task<List<User>> GetList()
        {
            IEnumerable<User> ret;
            using (var db = GetMySqlConnection())
            {
                const string sql = "select * from Users where";
                ret = await db.QueryAsync<User>(sql, commandType: CommandType.Text);
            }
            return ret.AsList();
        }

        /// <summary>
        /// 获取用户列表（条件）
        /// </summary>
        /// <param name="where">查询条件，and key1 = value1 and key2 lile %value2% ……</param>
        /// <returns></returns>
        public async Task<List<User>> GetList(string where)
        {
            IEnumerable<User> ret;
            using (var db = GetMySqlConnection())
            {
                string sql = $"select * from Users where 1 = 1 {where}";
                ret = await db.QueryAsync<User>(sql, commandType: CommandType.Text);
            }
            return ret.AsList();
        }

        public async Task<int> UpdateOrganizationIdAsync(string userId, string orgId)
        {
            int ret;
            using (var db = GetMySqlConnection())
            {
                ret = await db.ExecuteAsync("update Users set OrgId=@OrgId Where Id = @Id", new { Id = userId, OrgId = orgId }, commandType: CommandType.Text);
            }
            return ret;
        }

        public async Task<Tuple<List<Worker>, int>> GetWorkers(int pageSize, int pageIndex, string strWhere)
        {
            IEnumerable<Worker> ret;
            int total = 0;
            using (var db = GetMySqlConnection())
            {
                string sql = GetPagingSQL($"select u.*,(select COUNT(0) from WorkOrders wko where wko.WorkerID  = u.Id) as WorkOrderCount,(select avg(ServiceScore) from WorkOrders wo where wo.WorkerID=u.Id and wo.WorkOrderStatus=4) as Score,(select COUNT(0) from Organizations org where org.WorkerID = u.Id) as OrgCount from Users u where u.Usertype =2 {strWhere}", pageIndex, pageSize, "WorkOrderCount desc,CreateTime desc");
                string sqlcount = $"select count(0) from Users u where u.Usertype = 2 {strWhere}";
                ret = await db.QueryAsync<Worker>(sql, commandType: CommandType.Text);
                total = await db.QuerySingleAsync<int>(sqlcount, commandType: CommandType.Text);
            }
            return Tuple.Create(ret.AsList(), total);
        }

        public async Task<Tuple<List<UserView>, int>> GetUsers(int pageSize, int pageIndex, string strWhere)
        {
            IEnumerable<UserView> ret;
            int total = 0;
            using (var db = GetMySqlConnection())
            {
                string sql = GetPagingSQL($"select u.*,(select COUNT(0) from WorkOrders wko where wko.SubmitUserID  = u.Id) as WorkOrderCount,org.OrgName,org.AuditStatus,org.SubmitTime,org.WorkerId from Users u left join Organizations org on org.SubmitUserID = u.Id  where u.Usertype = 1 {strWhere}", pageIndex, pageSize, "org.AuditStatus desc,u.CreateTime desc");
                string sqlcount = $"select count(0) from Users u left join Organizations org on org.SubmitUserID = u.Id where u.Usertype = 1 {strWhere}";
                ret = await db.QueryAsync<UserView>(sql, commandType: CommandType.Text);
                total = await db.QuerySingleAsync<int>(sqlcount, commandType: CommandType.Text);
            }
            return Tuple.Create(ret.AsList(), total);
        }

        public async Task<DataSata> DataSata()
        {
            DataSata ret = new DataSata();
            using (var db = GetMySqlConnection())
            {
                string sqlUser = $"select count(0) as user from Users u where u.Usertype = 1;";
                string sqlWorker = $"select count(0) as user from Users u where u.Usertype = 2;";
                string sqlWorkOrder = $"select count(0) as workorder from WorkOrders;";
                string sqlPayment = $"select count(0) payment FROM  Payments;";
                ret.user = await db.QuerySingleAsync<int>(sqlUser, commandType: CommandType.Text);
                ret.worker = await db.QuerySingleAsync<int>(sqlWorker, commandType: CommandType.Text);
                ret.workorder = await db.QuerySingleAsync<int>(sqlWorkOrder, commandType: CommandType.Text);
                ret.payment = await db.QuerySingleAsync<int>(sqlPayment, commandType: CommandType.Text);
            }
            return ret;
        }

        /// <summary>
        /// 获取用户列表（条件，分页）
        /// </summary>
        /// <param name="pagesize">每页数量</param>
        /// <param name="pageindex">页码</param>
        /// <param name="where">查询条件，and key1 = value1 and key2 lile %value2% ……</param>
        /// <returns></returns>
        //public async Task<Tuple<List<UserView>, int>> GetList(int pagesize, int pageindex, string where)
        //{
        //    IEnumerable<UserView> ret;
        //    int total = 0;
        //    using (var db = GetMySqlConnection())
        //    {
        //        string sql = GetPagingSQL($"select *,(select COUNT(0) from UserDevice where UserDevice.UserId = Users.Id) as DeviceNum  from Users where 1 = 1 {where}", pageindex, pagesize);
        //        string sqlcount = $"select count(0) from Users where 1 = 1 {where}";
        //        ret = await db.QueryAsync<UserView>(sql, commandType: CommandType.Text);
        //        total = await db.QuerySingleAsync<int>(sqlcount, commandType: CommandType.Text);
        //    }
        //    return Tuple.Create(ret.AsList(), total);
        //}

    }
}